package com.lanyou.cook.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLTimeoutException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.google.common.primitives.Ints;
import com.google.gson.JsonObject;
import com.lanyou.cook.common.Constants;
import com.lanyou.cook.entity.domain.DBlink;
import com.lanyou.cook.entity.domain.FieldSystem;
import com.lanyou.cook.repository.DBlinkRepository;
import com.lanyou.cook.repository.FieldSystemRepository;
import com.lanyou.cook.service.param.ConnectOracleForImportParam;
import com.lanyou.cook.service.vo.DBLinkParseResult;

/**
 * 链接oracle数据库导入dblink控制层
 * @author 曾族根
 *
 */
@RestController
@RequestMapping(Constants.BASE_URL_API + "/connectOracles")
public class ConnectOracleForImport {
	@Autowired
	DBlinkRepository dblinkRepository;
	@Autowired
	FieldSystemRepository fieldSystemRepository;
	private String url;
	/**
	 * 链接oracle数据库导入dblink
	 * @param param 类型：ConnectOracleForImportParam
	 * @return 导入的结果
	 */
	@RequestMapping(value = "/link/oracle", method = RequestMethod.POST,produces = "application/json")
	@Transactional
	public String linkOracleForImport(@RequestBody ConnectOracleForImportParam param) {
		List<DBlink> dblinks = new ArrayList<DBlink>();
		Connection con = null;// @创建一个数据库连接
		PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
		ResultSet result = null;// 创建一个结果集对象
		JsonObject result2 = new JsonObject();
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
			url = "jdbc:oracle:" + "thin:@" + param.getOnwerHost() + ":" + param.getOwnerPort() + ":" + param.getOnwerServiceName();
			con = DriverManager.getConnection(url, param.getUserCode(),param.getPassword());// 获取连接
			String sql = "select DB_LINK,USERNAME,PASSWORD,HOST from user_db_links";// 预编译语句，“？”代表参数
			pre = con.prepareStatement(sql);// 实例化预编译语句
			result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
			System.out.println(result);
			while (result.next()) {
				String dblinkName = result.getString("DB_LINK");
				String userName = result.getString("USERNAME");
				String password = result.getString("PASSWORD");
				String host = result.getString("HOST");
				if(host!=null){
				host=host.replace(" ", "");//去除空格
				}
				System.out.println(host);
				DBLinkParseResult root = analyticalTns(host);
				DBlink dblink = dblinkRepository.findByNameAndOwnerHost(dblinkName, param.getOnwerHost());
				if(dblink==null){
					dblink = new DBlink();
				}
				dblink.setName(dblinkName);//dblink名
				dblink.setDestUserCode(userName);//目标账号
				dblink.setDestPassword(password);//密碼（空）
				FieldSystem fieldSystem = fieldSystemRepository.findByName(param.getFieldSystem());
				if(fieldSystem!=null){
					dblink.setFieldSystem(fieldSystem);//系統
				}else{
					result2.addProperty("backValue", param.getFieldSystem()+"不存在");
					break;
				}
				dblink.setOwnerServiceName(param.getOnwerServiceName());//源實例名
				dblink.setOwnerHost(param.getOnwerHost());//源地址
				dblink.setOwnerPort(param.getOwnerPort());//源端口
				dblink.setOwnerUser(param.getPassword());
				if(root.getType()==0x01){
					dblink.setServicePort(root.getPort());
				dblink.setServiceName(root.getServiceName());//服務實例名
				}else{
					if(root.getType()==0x02){
						dblink.setServicePort(root.getPort());
						dblink.setServiceName(root.getSID());
					}else{
						dblink.setServiceName(host);
					}
				}
				dblink.setHost(root.getHost());//host
				dblinks.add(dblink);
			}
			if(dblinks.isEmpty()){
				result2.addProperty("resultCode", "NULL");
				return result2.toString();
			}
			for(DBlink dbllink :dblinks){
				dblinkRepository.save(Arrays.asList(dbllink));
			}
			result2.addProperty("resultCode", "SUCCESS");
			return result2.toString();
		}catch(SQLTimeoutException e){
			result2.addProperty("resultCode", "TIMEOUT");
			return result2.toString();
		}catch(SQLException e){
			result2.addProperty("resultCode", "FALSE");
			return result2.toString();
		}
		catch(Exception e){
			e.printStackTrace();
		} 
		finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (result != null)
					result.close();
				if (pre != null)
					pre.close();
				if (con != null)
					con.close();
				System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return result2.toString();
	}
	/**
	 * 解析host
	 * @param tns 类型：String
	 * @return 解析出来的result
	 */
	// 解析host
	public DBLinkParseResult analyticalTns(String tns) {
		Pattern pattern_host = Pattern.compile("(\\w*)=.*?\\).*?\\(HOST=(.*?)\\)\\(");
		Pattern pattern_port = Pattern.compile("(\\w*)=.*?\\).*?\\(PORT=(.*?)\\)\\)\\)");// service_name方式的port匹配
		Pattern pattern_port_sid = Pattern.compile("(\\w*)=.*?\\).*?\\(PORT=(.*?)\\)\\)\\("); // SID方式的port匹配
		Pattern pattern_servicename = Pattern.compile("(\\w*)=.*?\\).*?\\(SERVICE_NAME=(.*?)\\)\\)\\)");
		Pattern pattern_sid = Pattern.compile("(\\w*)=.*?\\).*?\\(SID=(.*?)\\)\\)\\)");

		Matcher matcher_host = pattern_host.matcher(tns);
		Matcher matcher_port = pattern_port.matcher(tns);
		Matcher matcher_port_sid = pattern_port_sid.matcher(tns);
		Matcher matcher_servicename = pattern_servicename.matcher(tns);
		Matcher matcher_sid = pattern_sid.matcher(tns);

		String str_host = "";
		String str_port = "";
		String str_servicename = "";
		String str_sid = "";

		while (matcher_host.find()) {
			str_host = matcher_host.group(2);
		}

		while (matcher_servicename.find()) {
			str_servicename = matcher_servicename.group(2);
		}
		while (matcher_sid.find()) {
			str_sid = matcher_sid.group(2);
		}

		// sid不为空，表示采用SID的正则表达式来匹配端口
		if (str_sid != "") {
			while (matcher_port_sid.find()) {
				str_port = matcher_port_sid.group(2);
			}
		} else {
			while (matcher_port.find()) {
				str_port = matcher_port.group(2);
			}
		}

		Integer port = Ints.tryParse(str_port);
		if(str_host==""&&str_port==""&&str_servicename==""&&str_sid==""){
			DBLinkParseResult result = null;
			result=new DBLinkParseResult();
			result.setType(DBLinkParseResult.TYPE_NULL);
			return result;
		}else{
			if(str_host==""||str_port==""){
				return null;
			}else{
				if(str_servicename!="")
				{
					DBLinkParseResult result = null;
					result=new DBLinkParseResult();
					result.setType(DBLinkParseResult.TYPE_SERVICE_NAME);
					result.setHost(str_host);
					result.setPort(port);
					result.setServiceName(str_servicename);
					return result;
				}else{
					DBLinkParseResult result = null;
					result=new DBLinkParseResult();
					result.setHost(str_host);
					result.setPort(port);
					result.setType(DBLinkParseResult.TYPE_SID);
					result.setSID(str_sid);
					return result;
				}
			}
		}
	}
}